package com.bstek.uflo.form.view.table.dialect.impl;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.bstek.uflo.form.Constants;
import com.bstek.uflo.form.model.TableColumn;
import com.bstek.uflo.form.model.TableDefinition;

/**
 * @author Jacky.gao
 * @since 2013年11月7日
 */
@Component("com.bstek.uflo.form.view.table.dialect.impl.Db2Dialect")
public class Db2Dialect extends AbstractDialect {

	public boolean support(Connection connection) {
		return support(connection, "db2", null);
	}
	
	public String getPaginationSql(String sql, int pageNo, int pageSize) {
		int startNo = (pageNo - 1) * pageSize;
		int endNo = pageNo * pageSize;
		
		int startOfSelect = sql.toLowerCase().indexOf("select");

		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 )
				.append( sql.substring(0, startOfSelect) )	// add the comment
				.append("select * from ( select ") 			// nest the main query in an outer select
				.append( getRowNumber(sql) ); 				// add the rownnumber bit into the outer query select list

		if ( hasDistinct(sql) ) {
			pagingSelect.append(" row_.* from ( ")			// add another (inner) nested select
					.append( sql.substring(startOfSelect) ) // add the main query
					.append(" ) as row_"); 					// close off the inner nested select
		}
		else {
			pagingSelect.append( sql.substring( startOfSelect + 6 ) ); // add the main query
		}

		pagingSelect.append(" ) as temp_ where rownumber_ ");

		//add the restriction to the outer select
		pagingSelect.append("between "+startNo+" and "+endNo+"");
		return pagingSelect.toString();
	}
	
	private String getRowNumber(String sql) {
		StringBuffer rownumber = new StringBuffer(50)
			.append("rownumber() over(");

		int orderByIndex = sql.toLowerCase().indexOf("order by");

		if ( orderByIndex>0 && !hasDistinct(sql) ) {
			rownumber.append( sql.substring(orderByIndex) );
		}

		rownumber.append(") as rownumber_,");

		return rownumber.toString();
	}
	
	private static boolean hasDistinct(String sql) {
		return sql.toLowerCase().indexOf("select distinct")>=0;
	}

	public String[] generateCreateTableSchema(TableDefinition table) {
		String sql=generateSchema(table, false);
		List<TableDefinition> children=table.getSlaveTables();
		if(children!=null && children.size()>0){
			TableDefinition child=children.get(0);
			return new String[]{sql,generateSchema(child,true)};
		}
		return new String[]{sql};
	}
	
	private String generateSchema(TableDefinition table,boolean isChildren){
		StringBuffer sb=new StringBuffer();
		sb.append("create table "+table.getName()+"(");
		List<TableColumn> columns=table.getColumns();
		if(columns!=null){
			sb.append(Constants.PRIMARY_KEY_COLUMN+" bigint,");
			for(TableColumn col:columns){
				sb.append(col.getName()+" "+col.getDataType() +(col.isEmpty()?"":" not null"));	
				sb.append(",");
			}
			sb.append(""+Constants.STATE_COLUMN+" varchar(20),");
			if(isChildren){
				sb.append(""+Constants.MASTER_ID+" bigint,");				
			}else{
				sb.append(""+Constants.PROCESS_PROMOTER+" varchar(60),");								
			}
			sb.append("primary key ("+Constants.PRIMARY_KEY_COLUMN+")");
		}
		sb.append(");");
		return sb.toString();
	}
	
	public List<String> getDataTypes() {
		List<String> list=new ArrayList<String>();
		list.add("Varchar(30)");
		list.add("Date");
		list.add("Integer");
		list.add("SmallInt");
		list.add("Double");
		list.add("Float");
		return list;
	}
}
